package com.jr.dao.impl;

import com.jr.dao.IUsersDao;
import com.jr.pojo.Users;
import com.jr.pojo2.EmpSalUsers;
import com.jr.util.DBHelper;
import com.jr.util.PageHelper;

import java.sql.*;
import java.util.ArrayList;

public class UserDaoImpl implements IUsersDao {

    DBHelper db = new DBHelper();

    @Override // 分页查询 -- 总条数
    public int selectPageDataCount() {
        StringBuilder sql =new StringBuilder("select count(id) from users where 1=1");
        Connection con =null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            con = db.getCon();
            ps = con.prepareStatement(sql.toString());
            resultSet = ps.executeQuery();
            if (resultSet.next()) {
                return resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            db.closeAll(con,ps,resultSet);
        }
        return 0;
    }

    @Override // 分页查询 查询当前页面 展示集合
    public ArrayList<Users> selectIndexDate(PageHelper ph) {
        String sql="select * from users limit ?,?";
        int index = (ph.getPageIndex()-1)*ph.getShowNumber();
        return db.dtQuery(sql, Users.class, index, ph.getShowNumber());

    }

    @Override //按 用户名 查 分页
    public ArrayList<Users> selectByUserName(String userName, PageHelper ph) {
        String sql="select * from users where userName like ? limit ?,?";
        ArrayList list=new ArrayList();
        int index=(ph.getPageIndex()-1)*ph.getShowNumber();
        list.add("%"+userName+"%");
        list.add(index);
        list.add(ph.getShowNumber());
        return db.dtQuery(sql, Users.class,list.toArray());
    }

    @Override //按 手机号 查 分页
    public ArrayList<Users> selectByPhoneNumber(String phoneNumber, PageHelper ph) {
        String sql="select * from users where phoneNumber like ? limit ?,?";
        ArrayList list=new ArrayList();
        int index=(ph.getPageIndex()-1)*ph.getShowNumber();
        list.add("%"+phoneNumber+"%");
        list.add(index);
        list.add(ph.getShowNumber());
        return db.dtQuery(sql, Users.class,list.toArray());
    }

    @Override //按 邮箱 查 分页
    public ArrayList<Users> selectByEmail(String email, PageHelper ph) {
        String sql="select * from users where email like ? limit ?,?";
        ArrayList list=new ArrayList();
        int index=(ph.getPageIndex()-1)*ph.getShowNumber();
        list.add("%"+email+"%");
        list.add(index);
        list.add(ph.getShowNumber());
        return db.dtQuery(sql, Users.class,list.toArray());
    }

    @Override //查询全体用户
    public ArrayList<Users> selectAllUsers() {
        String sql = "select * from users";
        return db.dtQuery(sql, Users.class);
    }

    @Override //删除用--按employeeId 查 id
    public int selectIdByEmployeeId(Integer employeeId) {
        String sql = "select * from users where employeeId = ?";
        ArrayList<Users> list = db.dtQuery(sql, Users.class,employeeId);
        int id = list.size()>0?list.get(0).getId():0;
        return id;
    }

    @Override //用 name 登录，获得集合
    public ArrayList<Users> selectUsersByName(String userName) {
        String sql = "select * from users where userName=?";
        return db.dtQuery(sql, Users.class, userName);
    }

    @Override //创建用户？
    public int insertUsers(Users users) {
        /*(Integer id, String userName, String password,
                 Integer employeeId, String role, Timestamp createdAt,
                 Timestamp updatedAt, String sex,String address,
                 String phoneNumber, String email, String bornDate)*/
        String sql = "insert into users values(null,?,?,?,?,?,?,其他,?,?,?,?)";
        Object[] objs = {users.getUserName(),users.getPassword(),
                users.getEmployeeId(),users.getRole(),users.getCreatedAt(),
                users.getUpdatedAt(),users.getAddress(),
                users.getPhoneNumber(),users.getEmail(),users.getBornDate()};
        return db.dtUpdate(sql, objs);
    }

    @Override /* 添加 员工时 同时添加 */
    public int insertUserWithEmp(EmpSalUsers emp) {
        String sqlUser = "insert into users values(null,null,null,?,null,?,?,其他,null,?,?,null)";
        Object[] onjUser = {emp.getEmployeeId(),new Timestamp(System.currentTimeMillis()),new Timestamp(System.currentTimeMillis()),emp.getPhoneNumber(),emp.getEmail()};
        return db.dtUpdate(sqlUser,onjUser);
    }

    @Override //修改用户信息
    public int updateUsers(Users users) {
        String sql = "update from users set userName=?,employeeId=?,email=?,phoneNumber=?,sex=?, where id=?";
        Object[] objs = {};
        return db.dtUpdate(sql, objs);
    }

    @Override // 删除用户
    public int deleteUsersByUserName(String userName) {
        String sql = "delete from users where userName=?";
        return db.dtUpdate(sql, userName);
    }

    @Override
    public int deleteUsersByEmployeeId(Integer employeeId) {
        String sql = "delete from users where employeeId=?";
        return db.dtUpdate(sql, employeeId);
    }
}
